![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Tuning CheckpointsCheckpoints occur in the Oracle database to ensure that all dirty blocks in the SGA are eventually written to disk. Because the DBWR works on a least recently used (LRU) algorithm, data that has been untouched in the SGA for the longest time is written out first. This has the side effect that often-used data blocks may never be written out to disk. A checkpoint causes all the dirty blocks in the SGA to be written to disk. The following situations can cause a checkpoint:
Depending on how a checkpoint is invoked, a checkpoint can be either a normal or fast checkpoint. For a normal checkpoint, a few more blocks of data are written by the DBWR when it writes, on behalf of the checkpoint. For a fast checkpoint, a large number of blocks are written by the DBWR when it writes, on behalf of the checkpoint. These blocks are tagged on to the normal activity of the DBWR; while checkpoint blocks are being written, normal DBWR activity is still being done. In the case of the normal checkpoint, transaction processing is not as affected as it is with the fast checkpoint. With the normal checkpoint, more I/Os are required to complete the checkpoint but the throughput of the system is not drastically affected. Normal checkpoints are invoked by log switches and checkpoint intervals set up with the initialization parameters. If you notice a drop in performance as a normal checkpoint completes, this can sometimes be compensated for by enabling the CKPT process. Doing so can help if the performance drop is caused by the LGWR process being too busy updating file headers. CKPT updates the file headers, leaving LGWR free to write out the redo entries. To enable the CKPT process, set the initialization parameter CHECKPOINT_PROCESS to TRUE. With fast checkpoints, transaction processing is affected more drastically. Because a large number of blocks are added to the DBWRs normal activity, the number of I/Os are reduced but the DBWR is affected by this process. Because the DBWR is busy with the checkpoint, its normal activities are usually affected, causing increased response times and decreased throughput. Fast checkpoints are caused by online tablespace backups, instance shutdowns, and administrator-forced checkpoints. If you are not as interested in recovery time as you are in throughput, you can set the checkpoint frequency so that a checkpoint is performed only when the redo log switch occurs. If you are interested in quick recovery, set the checkpoint frequency more often. To further increase the checkpoint interval, increase the size of the redo log files.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |